﻿using System;
using System.Collections.Generic;
using System.Text;
using HZ.Supervise;
using HZ.Supervise.IBLL;
using HZ.Common;
using HZ.Supervise.DAL;


namespace HZ.Supervise.BLL
{
    public class BLL_Statistics : MarshalByRefObject, IBLL_Statistics
    {

        /// <summary>
        /// 获取每个企业总汇总信息
        /// </summary>
        /// <param name="where"></param>
        /// <param name="currenpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public IList<Model.View.Statistics_Company> GetStatistics_CompanyInfo(Model.Query.Statistics_Company where, int currenpage, int pagesize, out int totalrecord)
        {
            ThrowHelper.Throw(where.CompanyID_PS != null, "where.CompanyID_PS != null,");
            return DAL_Helper.GetPagedView<Model.View.Statistics_Company, Model.Query.Statistics_Company>(where, currenpage, pagesize, out totalrecord);
        }

        /// <summary>
        /// 获取每个企业各家医院总汇总信息
        /// </summary>
        /// <param name="where"></param>
        /// <param name="currenpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public IList<Model.View.Statistics_Company_Hospital> GetStatistics_Company_Hospital(Model.Query.Statistics_Company_Hospital where, int currenpage, int pagesize, out int totalrecord)
        {
             return DAL_Helper.GetPagedView<Model.View.Statistics_Company_Hospital, Model.Query.Statistics_Company_Hospital>(where, currenpage, pagesize, out totalrecord);
        }

        /// <summary>
        /// 获取每个医院所有企业汇总信息
        /// </summary>
        /// <param name="where"></param>
        /// <param name="currenpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public IList<Model.View.Statistics_Hospital> GetStatistics_Hospital(Model.Query.Statistics_Hospital where, int currenpage, int pagesize, out int totalrecord)
        {

            return DAL_Helper.GetPagedView<Model.View.Statistics_Hospital, Model.Query.Statistics_Hospital>(where, currenpage, pagesize, out totalrecord);
        }


        /// <summary>
        /// 获取具体交易明细信息
        /// </summary>
        /// <param name="where"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public IList<HZ.Supervise.Model.Supervise> GetTradeInfo(HZ.Supervise.Model.SuperviseQuery where, int currentpage, int pagesize, out int totalrecord)
        {

            return DAL_Helper.GetPagedView<HZ.Supervise.Model.Supervise, HZ.Supervise.Model.SuperviseQuery>(where, currentpage, pagesize, out totalrecord);
        }

        public System.Data.DataTable GetHospitalAmountStatisticsGroupByArea(string areaid, DateTime? timeFrom, DateTime? timeTo,string strwhere)
        {
            string strHospitalWhere = "";
            if (!String.IsNullOrEmpty(strwhere))
            {
                strHospitalWhere = string.Format(" and HospitalID in(select HospitalID from tb_Hospital where areaid in({0}) {1})",areaid,strwhere);
            }
            string strselect = string.Format(@"SELECT HospitalID ,HospitalName,
                                                sum(WarehouseAmount) WarehouseAmount,
                                                WarehouseMonth,
                                                sum(ReturnAmount)ReturnAmount ,
                                                AreaID ,
                                                AreaName ,
                                                sum(ISNULL(WarehouseAmount, 0) - ISNULL(ReturnAmount, 0)) PayAmount FROM 
                                                (SELECT    HospitalID ,
                                                                    HospitalName ,
                                                                    SUM(WarehouseCount * Price) AS WarehouseAmount ,
                                                                    SUBSTRING(CONVERT(VARCHAR(12), WarehouseTime, 23), 0, 8) AS WarehouseMonth ,
                                                                    '' ReturnMonth ,
                                                                    0 ReturnAmount,MAX(AreaID) AreaID,MAX(AreaName) AreaName 
                                                          FROM      dbo.TB_Distribution
                                                          WHERE     AreaID IN ({0})
                                                                    AND WarehouseTime > '{1}'
                                                                    AND WarehouseTime < '{2}'
                                                                    AND DistributeState > 0 {3}
                                                          GROUP BY  HospitalID ,
                                                                    HospitalName ,
                                                                    SUBSTRING(CONVERT(VARCHAR(12), WarehouseTime, 23), 0, 8)
                                                          UNION
                                                          SELECT    a.HospitalID,
                                                                    B.HospitalName,a.WarehouseAmount,WarehouseMonth,ReturnMonth ,ReturnAmount , AreaID,AreaName
                                                          FROM      ( SELECT    HospitalID ,
                                                                                0 WarehouseAmount ,
                                                                                SUBSTRING(CONVERT(VARCHAR(12), OverTime, 23), 0, 8) AS WarehouseMonth ,
                                                                                SUM(RetrunPrice * ReturnCount) AS ReturnAmount ,
                                                                                SUBSTRING(CONVERT(VARCHAR(12), OverTime, 23),
                                                                                          0, 8) AS ReturnMonth 
                                                                      FROM      dbo.TB_ReturnMerchandiseDetail
                                                                      WHERE     ReturnState = 3
                                                                                AND OverTime > '{1}'
                                                                                AND OverTime < '{2}' 
                                                                      GROUP BY  HospitalID ,
                                                                                SUBSTRING(CONVERT(VARCHAR(12), OverTime, 23),
                                                                                          0, 8)
                                                                    ) A
                                                                    INNER JOIN (SELECT HospitalID ,TB_Area.AreaID,TB_Area.AreaName,
                                                                                        HospitalName
                                                                                 FROM   dbo.TB_Hospital left join TB_Area on TB_Hospital.AreaID = TB_Area.AreaID
                                                                                 WHERE  TB_Hospital.AreaID IN ({0}) {4}
                                                                               ) B ON A.HospitalID = B.HospitalID
                                                                               
                                                                               ) AA group by HospitalID,HospitalName ,WarehouseMonth,AreaID ,AreaName 
                                                                                ORDER BY AreaID,WarehouseMonth
                                                                                            ", areaid,timeFrom,timeTo,strHospitalWhere,strwhere);
            System.Data.DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(strselect);
            return dt;
        }
        public System.Data.DataTable GetHospitalAmountStatisticsGroupByAreaTotal(string areaid, DateTime? timeFrom, DateTime? timeTo, string strwhere)
        {
            string strHospitalWhere = "";
            if (!String.IsNullOrEmpty(strwhere))
            {
                strHospitalWhere = string.Format(" and HospitalID in(select HospitalID from tb_Hospital where areaid in({0}) {1})", areaid, strwhere);
            }
            string strselect = string.Format(@"SELECT  AreaID, AreaName, 
                                                ISNULL(SUM(WarehouseAmount), 0) AS WarehouseAmount ,
                                                ISNULL(SUM(ReturnAmount), 0) AS ReturnAmount ,
                                                ISNULL(SUM(WarehouseAmount), 0) - ISNULL(SUM(ReturnAmount), 0) AS PayAmount FROM 
                                                (SELECT    HospitalID ,
                                                                    HospitalName ,
                                                                    SUM(WarehouseCount * Price) AS WarehouseAmount ,
                                                                    SUBSTRING(CONVERT(VARCHAR(12), WarehouseTime, 23), 0, 8) AS WarehouseMonth ,
                                                                    '' ReturnMonth ,
                                                                    0 ReturnAmount,MAX(AreaID) AreaID,MAX(AreaName) AreaName 
                                                          FROM      dbo.TB_Distribution
                                                          WHERE     AreaID IN ({0})
                                                                    AND WarehouseTime > '{1}'
                                                                    AND WarehouseTime < '{2}'
                                                                    AND DistributeState > 0 {3}
                                                          GROUP BY  HospitalID ,
                                                                    HospitalName ,
                                                                    SUBSTRING(CONVERT(VARCHAR(12), WarehouseTime, 23), 0, 8)
                                                          UNION
                                                          SELECT    a.HospitalID,
                                                                    B.HospitalName,a.WarehouseAmount,WarehouseMonth,ReturnMonth ,ReturnAmount ,AreaID,AreaName
                                                          FROM      ( SELECT    HospitalID ,
                                                                                0 WarehouseAmount ,
                                                                                SUBSTRING(CONVERT(VARCHAR(12), OverTime, 23), 0, 8) AS WarehouseMonth ,
                                                                                SUM(RetrunPrice * ReturnCount) AS ReturnAmount ,
                                                                                SUBSTRING(CONVERT(VARCHAR(12), OverTime, 23),
                                                                                          0, 8) AS ReturnMonth 
                                                                      FROM      dbo.TB_ReturnMerchandiseDetail
                                                                      WHERE     ReturnState = 3
                                                                                AND OverTime > '{1}'
                                                                                AND OverTime < '{2}' 
                                                                      GROUP BY  HospitalID ,
                                                                                SUBSTRING(CONVERT(VARCHAR(12), OverTime, 23),
                                                                                          0, 8)
                                                                    ) A
                                                                    INNER JOIN (SELECT HospitalID ,TB_Area.AreaID,TB_Area.AreaName,
                                                                                        HospitalName
                                                                                 FROM   dbo.TB_Hospital left join TB_Area on TB_Hospital.AreaID = TB_Area.AreaID
                                                                                 WHERE  TB_Hospital.AreaID  IN ({0}) {4}
                                                                               ) B ON A.HospitalID = B.HospitalID
                                                                               
                                                                               ) AA group by AreaID ,AreaName     
                                            ", areaid, timeFrom, timeTo, strHospitalWhere, strwhere);
            System.Data.DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(strselect);
            return dt;
        }

        public System.Data.DataTable GetHospitalAmountStatistics(string areaid, DateTime? timeFrom, DateTime? timeTo, string strwhere)
        {
            string strHospitalWhere = "";
            if (!String.IsNullOrEmpty(strwhere))
            {
                strHospitalWhere = string.Format(" and HospitalID in(select HospitalID from tb_Hospital where areaid in({0}) {1})", areaid, strwhere);
            }
            string strselect = string.Format(@" SELECT  COUNT(DISTINCT HospitalID)  hospitalcount,SUM( WarehouseAmount -ReturnAmount) payamount
                                                FROM    ( SELECT    HospitalID ,
                                                                    HospitalName ,
                                                                    SUM(WarehouseCount * Price) AS WarehouseAmount ,
                                                                    SUBSTRING(CONVERT(VARCHAR(12), WarehouseTime, 23), 0, 8) AS WarehouseMonth ,
                                                                    '' ReturnMonth ,
                                                                    0 ReturnAmount
                                                          FROM      dbo.TB_Distribution
                                                          WHERE     AreaID IN ({0})
                                                                    AND WarehouseTime > '{1}'
                                                                    AND WarehouseTime < '{2}'
                                                                    AND DistributeState > 0 {3}
                                                          GROUP BY  HospitalID ,
                                                                    HospitalName ,
                                                                    SUBSTRING(CONVERT(VARCHAR(12), WarehouseTime, 23), 0, 8)
                                                          UNION
                                                          SELECT    a.HospitalID,
                                                                    B.HospitalName,a.WarehouseAmount,WarehouseMonth,ReturnMonth ,ReturnAmount
                                                          FROM      ( SELECT    HospitalID ,
                                                                                0 WarehouseAmount ,
                                                                                '' WarehouseMonth ,
                                                                                SUM(RetrunPrice * ReturnCount) AS ReturnAmount ,
                                                                                SUBSTRING(CONVERT(VARCHAR(12), OverTime, 23),
                                                                                          0, 8) AS ReturnMonth
                                                                      FROM      dbo.TB_ReturnMerchandiseDetail
                                                                      WHERE     ReturnState = 3
                                                                                AND OverTime > '{1}'
                                                                                AND OverTime <'{2}'
                                                                      GROUP BY  HospitalID ,
                                                                                SUBSTRING(CONVERT(VARCHAR(12), OverTime, 23),
                                                                                          0, 8)
                                                                    ) A
                                                                    INNER JOIN ( SELECT HospitalID ,
                                                                                        HospitalName
                                                                                 FROM   dbo.TB_Hospital
                                                                                 WHERE  AreaID IN ( {0}) {4}
                                                                               ) B ON A.HospitalID = B.HospitalID
                                                        ) TA       
", areaid, timeFrom, timeTo,strHospitalWhere,strwhere);
            System.Data.DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(strselect);
            return dt;
        }

        public System.Data.DataTable GetCompanyAmountStatistics(string companyid_ps, DateTime? timeFrom, DateTime? timeTo, string strwhere)
        {
            string strHospitalWhere = "";
            if (!String.IsNullOrEmpty(strwhere))
            {
                strHospitalWhere = string.Format(" and HospitalID in(select HospitalID from tb_Hospital where 1=1 {0})", strwhere);
            }
            string strselect = string.Format(@" SELECT  ISNULL(tw.CompanyID_PS, tr.CompanyID) AS CompanyID ,
        ISNULL(tw.CompanyName_PS, tr.CompanyName) AS CompanyName ,
        ISNULL(tw.WarehouseMonth, tr.ReturnMonth) AS WarehouseMonth ,
        ISNULL(tw.WarehouseAmount, 0) AS WarehouseAmount ,
        ISNULL(tr.ReturnAmount, 0) AS ReturnAmount ,
        ISNULL(tw.WarehouseAmount, 0) - ISNULL(tr.ReturnAmount, 0) AS PayAmount
FROM    ( SELECT    CompanyID_PS ,
                    CompanyName_PS ,
                    SUM(WarehouseCount * Price) AS WarehouseAmount ,
                    SUBSTRING(CONVERT(VARCHAR(12), WarehouseTime, 23), 0, 8) AS WarehouseMonth
          FROM      dbo.TB_Distribution
          WHERE     CompanyID_PS = '{0}'
                    AND WarehouseTime > '{1}'
                    AND WarehouseTime < '{2}'
                    AND DistributeState > 0 {3}
          GROUP BY  CompanyID_PS ,
                    CompanyName_PS ,
                    SUBSTRING(CONVERT(VARCHAR(12), WarehouseTime, 23), 0, 8)
        ) TW
        FULL JOIN ( SELECT  A.* ,
                            B.CompanyName
                    FROM    ( SELECT    CompanyID ,
                                        SUM(RetrunPrice * ReturnCount) AS ReturnAmount ,
                                        SUBSTRING(CONVERT(VARCHAR(12), OverTime, 23),
                                                  0, 8) AS ReturnMonth
                              FROM      dbo.TB_ReturnMerchandiseDetail
                              WHERE     ReturnState = 3
                                        AND CompanyID = '{0}'
                                        AND OverTime > '{1}'
                                        AND OverTime < '{2}' {3}
                              GROUP BY  CompanyID ,
                                        SUBSTRING(CONVERT(VARCHAR(12), OverTime, 23),
                                                  0, 8)
                            ) A
                            INNER JOIN dbo.TB_Company B ON A.CompanyID = B.CompanyID
                  ) TR ON TW.CompanyID_PS = TR.CompanyID
                          AND tw.WarehouseMonth = TR.ReturnMonth  ORDER BY tw.WarehouseMonth,tr.ReturnMonth      
", companyid_ps, timeFrom, timeTo, strHospitalWhere);
            System.Data.DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(strselect);
            return dt;
        }

        /// <summary>
        /// 打印配送企业支付单
        /// </summary>
        /// <param name="vs"></param>
        /// <returns></returns>
        public System.Data.DataTable GetCompanyAmountStatistics(HZ.Supervise.Model.SuperviseQuery vs)
        {
            string startTime = "2001-1-1";
            string endTime = "2066-12-1";

            if (!string.IsNullOrEmpty(Convert.ToString(vs.CenSubmiitTimeFrom)))
            {

                startTime = vs.CenSubmiitTimeFrom.ToString();

            }
            if (!string.IsNullOrEmpty(Convert.ToString(vs.CenSubmiitTimeTo)))
            {
                endTime = vs.CenSubmiitTimeTo.ToString(); 
            }
            string where = string.Empty;
            string strPro = "";
            string hos = "";
            if (!string.IsNullOrEmpty(vs.AreaID))
            {
                where += vs.AreaID;
            }
            if (!string.IsNullOrEmpty(Convert.ToString(vs.SortSource)))
            {
                if (vs.SortSource == 1)
                {
                    strPro = string.Format(" AND SortSource IN ({0})", "'1','2','3'");
                }
                else if (vs.SortSource == 4)
                {
                    strPro = string.Format("   AND SortSource IN ({0}) ", "'4'");
                }
            }
            if (!string.IsNullOrEmpty(Convert.ToString (vs.IsEssential)))
            {
                strPro += string.Format(" AND IsEssential = '{0}'",vs.IsEssential);
            }
            if (!string.IsNullOrEmpty(vs.IsCountry))
            {
                hos += string.Format(" AND IsCountryDetail IN ({0})", vs.IsCountry);
            }
            if (!string.IsNullOrEmpty(vs.IsGovernment))
            {
                hos += string.Format("AND IsGovernment = {0}",vs.IsGovernment);
            }

            string strselect = string.Format(@"SELECT  TR.CompanyID_PS CompanyID,
                                                    TR.CompanyName_PS CompanyName,
                                                    WarehouseMonth ,
                                                    SUM(WarehouseAmount) warehouseamount ,
                                                    SUM(returnamount) returnamount,SUM(WarehouseAmount-returnamount) PayAmount
                                            FROM    ( SELECT    CompanyID_PS ,
                                                                CompanyName_PS ,
                                                                ISNULL(SUM(WarehouseCount * Price),0) AS WarehouseAmount ,
                                                                0 returnamount ,
                                                                SUBSTRING(CONVERT(VARCHAR(12), WarehouseTime, 23), 0, 8) AS WarehouseMonth
                                                      FROM      dbo.TB_Distribution
                                                      WHERE     WarehouseTime > '{0}'
                                                                AND WarehouseTime < '{1}'
                                                                AND DistributeState > 0
                                                                {2}
                                                                AND ProcureCatalogID IN (
                                                                SELECT  ProcureCatalogID
                                                                FROM    dbo.View_ProcureCatalog_TB
                                                                WHERE   1 = 1 {3})
                                                                AND HospitalID IN ( SELECT  HospitalID
                                                                                    FROM    tb_Hospital
                                                                                    WHERE   1 = 1 {4})
                                                      GROUP BY  CompanyID_PS ,
                                                                CompanyName_PS ,
                                                                SUBSTRING(CONVERT(VARCHAR(12), WarehouseTime, 23), 0, 8)
                                                      UNION
                                                      SELECT    A.CompanyID ,
                                                                B.CompanyName ,
                                                                0 WarehouseAmount ,
                                                                a.ReturnAmount ,
                                                                A.ReturnMonth
                                                      FROM      ( SELECT    CompanyID ,
                                                                            ISNULL(SUM(B.Price * A.ReturnCount),0)  ReturnAmount ,
                                                                            SUBSTRING(CONVERT(VARCHAR(12), OverTime, 23),
                                                                                      0, 8) AS ReturnMonth
                                                                  FROM      dbo.TB_ReturnMerchandiseDetail A
                                                                            INNER JOIN VIEW_Distribution B ON A.DistributionSerialID = B.DistributionSerialID
                                                                  WHERE     1 = 1
                                                                            AND ReturnState = 3
                                                                            AND OverTime > '{0}'
                                                                            AND OverTime < '{1}'
                                                                            {2}
                                                                            AND A.HospitalID IN ( SELECT  HospitalID
                                                                                                  FROM    dbo.TB_Hospital
                                                                                                  WHERE   1 = 1 {4})
                                                                            AND A.ProcureCatalogID IN (
                                                                            SELECT  ProcureCatalogID
                                                                            FROM    dbo.View_ProcureCatalog_TB
                                                                            WHERE   1 = 1 {3} )
                                                                  GROUP BY  CompanyID ,
                                                                            SUBSTRING(CONVERT(VARCHAR(12), OverTime, 23),
                                                                                      0, 8)
                                                                ) A
                                                                INNER JOIN dbo.TB_Company B ON A.CompanyID = B.CompanyID
                                                    ) TR
                                            WHERE   TR.CompanyName_PS LIKE '%{5}%'
                                            GROUP BY TR.CompanyID_PS ,
                                                    TR.CompanyName_PS ,
                                                    WarehouseMonth 

                                                ", startTime , endTime, where , strPro,hos,vs.CompanyName_PS);
            System.Data.DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(strselect);
            return dt;
        }

    }
}
